﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;

/// <summary>
/// Summary description for DataLayer
/// </summary>
    public class DataLayer
    {
        // Perform SELECT statement and return a datareader containing results 
        public static SqlDataReader DoSelect(String SQLString, ref SqlConnection con)
        {
            //We have to pass in a connection here as we can't close it while the DataReader is in use.
            SqlCommand cmd;
            SqlDataReader dr = null;  //DataReader has no constructor; set it to NULL in case of error

            try
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd = new SqlCommand(SQLString, con);
                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                //Connection failed to open
                HttpContext.Current.Trace.Write("Error: " + ex.Message.ToString());

            }
            return dr;
        }
        public static int ExecuteSQL(String strSQL)
        {
            SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["CFACollectorConnectionString"].ToString());
            SqlCommand oSQLCommand = new SqlCommand(strSQL, Connection);
            Connection.Open();
            int i = oSQLCommand.ExecuteNonQuery();
            Connection.Close();
            return i;

        }

        public static bool InsertFollow(CookieFreeAnalyticsCollector.VisitorTracked inboundTrack)
        {
            // Used CleanInputText on query string and user agent to prevent SQL injection
            string sql = "insert into [RawLog] (RawQueryString,RemoteServerIP,ClientIP,UserAgent,ClientSessionID) values('" + CleanInputTextEmailSafe(inboundTrack.QueryString, false) + "','" + inboundTrack.RemoteServerIP + "','" + inboundTrack.ClientIP + "','" + CleanInputTextEmailSafe(inboundTrack.UserAgent, false) + "','" + CleanInputTextEmailSafe(inboundTrack.ClientSessionID, false) + "')";
            DataLayer.ExecuteSQL(sql);
            return true;
        }

        public static DataSet GetDataSet(String strSQL)
        {

            //HttpContext.Current.Trace.Write("In GetDataset")

            SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionStringReadOnly"].ToString());

            SqlCommand oSQLCommand = new SqlCommand(strSQL, Connection);
            SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(oSQLCommand);
            DataSet oDataset = new DataSet();

            oSqlDataAdapter.SelectCommand = oSQLCommand;
            oSqlDataAdapter.Fill(oDataset);

            Connection.Close();
            return oDataset;

        }

        public static String CleanInputTextEmailSafe(String inputstr, bool useblacklist)
        {
            //Invalid chars to replace
            if (inputstr == null)
            {
                return "";
            }
            string[] replacelist = { "--", ";--", ";", "/*", "*/", "@@" };
            for (int i = 0; i < replacelist.Length; i++)
            {
                inputstr = inputstr.Replace(replacelist[i], "");
            }

            //Replace apostrophe with double apostrophe
            inputstr = inputstr.Replace("'", "''");

            if (useblacklist)
            {
                //Create blacklist of common terms that might be used in a SQL attack
                string[] blackList = {"char ","nchar ",
                "varchar ","nvarchar ","alter ","cast ","create ","cursor ","declare ",
                "delete ","drop ", "exec ","execute ","fetch ","insert ","kill ",
                "select ", "sysobjects","syscolumns","table ","update "};
                CompareInfo comparer = CultureInfo.InvariantCulture.CompareInfo;

                for (int i = 0; i < blackList.Length; i++)
                {
                    if (comparer.IndexOf(inputstr, blackList[i], CompareOptions.IgnoreCase) >= 0)
                    {
                        //
                        //Handle the discovery of suspicious Sql characters here
                        inputstr = null;
                        break;
                    }
                }
            }
            return inputstr;

        }
    }